﻿using Ledict.ConditionExtract.Common;
using Ledict.ConditionExtract.IDAL;
using Ledict.Core.Data;
using System;
using System.Data;
using System.Data.Common;
using System.Text;
namespace Ledict.ConditionExtract.DAL
{


    /// <summary>
    /// Tree数据操作
    /// </summary>
    public class TreeDateDAL 
    {
        private string DbName;
        private string Case;
        public TreeDateDAL()
        {
            DbName = "JudgeDoc";
            Case = "Caselibrary";
        }

        #region 第一棵树
        /// <summary>
        /// 得到节点的下级
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable NodeTree_Tree(string pid)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetCauseTreeOne");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }


        /// <summary>
        /// 得到节点的下级
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable GetSourceTree(string pid)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetSourceTree");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 得到节点的下级(SYS_CaseType_DocType) add by zhangpeng 2016-09-12 
        /// </summary>
        /// <param name="ctCode"></param>
        /// <param name="cdDocType"></param>
        /// <param name="cdFileType"></param>
        /// <returns></returns>
        public DataTable GetSourceTree_New(string condition)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append(@"select * from [SYS_CaseType_DocType] where 1 = 1 ");
            //ljj 2017-9-14 修改
            //应业务调整，SYS_CaseType_DocType做出了调整，等通知发布更新
            string strSql = $"select * from (select  a.CT_Code, b.CD_ID,b.CD_Name,b.CD_Code,b.CD_DocType,a.CDR_IsAnalysableDoc from SYS_CaseTypeDocRel a inner join SYS_CaseType_DocType b on a.CD_Code = b.CD_Code) t where {condition}";
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            else
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// 下级来源树
        /// </summary>
        /// <param name="pId">父ID</param>
        /// <param name="level">层级,默认是1</param>
        /// <returns></returns>
        public DataTable GetSourceTreeChild(string pId, int level = 1)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
               select * from [SYS_CaseType_DocType_LogicParag] where 1 = 1 ");
            //第一级查询CD_ID
            if (level == 1)
            {
                strSql.Append(string.Format(@" and [CDL_Level] = '{1}' and CD_ID = '{0}'", pId, level));
            }
            else //第二级及以下节点查询parentId
            {
                strSql.Append(string.Format(@" and [CDL_Level] = '{1}' and CDL_ParentID = '{0}'", pId, level));
            }

            strSql.Append(" order by CDL_ShowIndex asc ");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            DataTable otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            else
            {
                return new DataTable();
            }
        }


        /// <summary>
        /// 得到节点的下级个数
        /// </summary>
        /// <param name="pid"></param>                 
        /// <returns></returns>
        public int GetChildType(string pid, int ptype, int TableName)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetChildType");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@ptype", DbType.Int32, ptype);
            oBase.AddInParameter(oCmd, "@TableName", DbType.Int32, TableName);

            return Convert.ToInt32(oBase.ExecuteScalar(oCmd));


        }


        #endregion

        #region 第二课树
        /// <summary>
        /// 得到节点的下级
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable GetCauseTree_Two(string pid, int effective)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetCauseTreeTwo");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@effective", DbType.Int32, effective);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetCauseTreeCombineExpress(string pid)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            string sql = string.Format(@"   select  a.MS_ID as id ,SituationName as name ,SituationType as CC_NodeType , isnull(IsMatch,2) as ES_IsMakeUp    
  , 1 as TableName   from dbo.SYS_Express_MatchSituation a where CHARINDEX(',{0},',','+AllStituationIDs+',')>0 and SituationName like '%+%' and CC_IsShowExpress=1  ", pid);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
        }

        public DataTable GetCauseTreeTwo_ToExpressTab(string pid, int effective)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetCauseTreeTwo_ToExpressTab");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@effective", DbType.Int32, effective);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetCauseTreeTwo_ToExpressTab_Virtual(string pid, int CC_IsCombination)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
      if(@CC_IsCombination=1)
      select MS_ID as id ,SituationName as name ,SituationType as CC_NodeType , isnull(IsMatch,2) as ES_IsMakeUp  
      , 0 as TableName from dbo.SYS_Express_MatchSituation
      where SituationType=9 and  MS_ID like'_'+@pid 
     else 
     select MS_ID as id ,SituationName as name ,SituationType as CC_NodeType , isnull(IsMatch,2) as ES_IsMakeUp  
      , 0 as TableName from dbo.SYS_Express_MatchSituation
      where SituationType=9 and  MS_ID like'_'+@pid and SituationName<>'组合'
");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@CC_IsCombination", DbType.Int32, CC_IsCombination);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public DataTable GetCauseTreeThree_ToExpressTab_Virtual(string pid, int CC_IsCombination)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
      if(@CC_IsCombination=1)
      select MS_ID as id ,SituationName as name ,SituationType as CC_NodeType , isnull(IsMatch,2) as ES_IsMakeUp  
      , 1 as TableName from dbo.SYS_Express_MatchSituation
      where SituationType=9 and  MS_ID like'_'+@pid  and CC_IsShowExpress=1
  else 
  select MS_ID as id ,SituationName as name ,SituationType as CC_NodeType , isnull(IsMatch,2) as ES_IsMakeUp  
      , 1 as TableName from dbo.SYS_Express_MatchSituation
      where SituationType=9 and  MS_ID like'_'+@pid  and CC_IsShowExpress=1 and SituationName<>'组合'

");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@CC_IsCombination", DbType.Int32, CC_IsCombination);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        #endregion

        #region 案由树
        /// <summary>
        /// 得到节点的下级
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public DataTable GetTreeHeadWords(string pid, string Tname)
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetTreeHeadWords");
            oBase.AddInParameter(oCmd, "@pid", DbType.String, pid);
            oBase.AddInParameter(oCmd, "@Tname", DbType.String, Tname);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }

        public string GetHeadWordAllPid(string headword)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(@"select AllParentId  from SYS_CaseCause where  CC_ID = @headword");
            oBase.AddInParameter(oCmd, "@headword", DbType.String, headword);
            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        }


        /// <summary>
        /// 根据案由ID字符串，获取案由的所有父级节点列表 add by zhangpeng
        /// </summary>
        /// <param name="headwords">案由ID字符串，eg：1181,1655</param>
        /// <returns></returns>
        public DataTable GetHeadWordAllPids(string headwords)
        {
            if (string.IsNullOrEmpty(headwords)) return null;
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(string.Format(@"select AllParentId  from SYS_CaseCause where  CC_ID in ({0})", headwords));

            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }



        #endregion


        /// <summary>
        /// 查新节点树
        /// </summary>
        /// <param name="FirPid"></param>
        /// <param name="name"></param>
        /// <param name="TreeNumber"></param>
        /// <returns></returns>
        public DataTable NodeTree_TreeQuery(string FirPid, string name, int TreeNumber)
        {

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_TreeNodeQuery");
            oBase.AddInParameter(oCmd, "@FirPid", DbType.String, FirPid);
            oBase.AddInParameter(oCmd, "@name", DbType.String, name);
            oBase.AddInParameter(oCmd, "@TreeNumber", DbType.String, TreeNumber);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }
        }



        public string GetAllParentNode(string id)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetStoredProcCommand("sp_CondiExt_GetAllParentNode");
            oBase.AddInParameter(oCmd, "@id", DbType.String, id);

            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd).ToString());

        }


        /// <summary>
        /// 根据父节点，获取案例类别
        /// </summary>
        /// <param name="parentId">父节点</param>
        /// <returns></returns>
        public DataTable GetCaseCategory(string parentId)
        {
            //if (string.IsNullOrEmpty(parentId)) return null;
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            var strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM [t_Case_Category] where 1 = 1 ");
            strSql.Append(string.IsNullOrEmpty(parentId) ? " and  parentid is null " : " and  parentid = " + parentId);
            strSql.Append(" order by sortNo asc ");
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;

        }

        /// <summary>
        /// 根据节点名称，获取当前节点及所有子节点
        /// </summary>
        /// <param name="ccName">节点名称</param>
        /// <returns></returns>
        public DataTable AllGetCaseCause(string ccName)
        {
            //if (string.IsNullOrEmpty(parentId)) return null;
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            var strSql = new StringBuilder();
            strSql.Append(@"declare @rootCcId varchar(20) 
	                        select @rootCcId = CC_ID from SYS_CaseCause 
	                        where CC_Name = '" + ccName + @"'
   
                            select cc_id,cc_name,cc_code,CC_ParentID,AllParentId from SYS_CaseCause
	                        where ','+AllParentId+',' like'%,'+@rootCcId+',%'
	                        order by cc_id asc");

            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;

        }

        /// <summary>
        /// 根据CCID，获取当前节点及所有子节点
        /// </summary>
        /// <param name="ccName">节点名称</param>
        /// <returns></returns>
        public DataTable AllGetCaseCauseByCCID(string ccId)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            string sql = string.Format(@" select cc_id,cc_name,cc_code,CC_ParentID,AllParentId,CC_SerialNumb,AllParentName,CC_NodeType from SYS_CaseCause
	                        where ','+AllParentId+',' like'%,{0},%'
	                        order by cc_id asc ", ccId);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;

        }


        /// <summary>
        /// 根据案例数据库的文书类型
        /// </summary>
        /// <returns></returns>
        public DataTable GetCaseProperty()
        {
            Database oBase = DatabaseFactory.CreateDatabase(Case);
            DbCommand oCmd = oBase.GetSqlStringCommand("     select  Id as ID, CasePropertyName as Name from t_Case_Property ");
            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }


        public DataTable GetCaseProperty1()
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(@"   			select OLR_CaseCauseID,OLR_SituationID,OLR_LawID,OLR_Law_ProvID,OLR_Law_ClauseNumber,OLR_Law_ItemNumber,count(1)as cout
            from SYS_QuoteLawRel where OLR_SituationID <> '' group by OLR_CaseCauseID, OLR_LawID, OLR_Law_ProvID, OLR_SituationID, OLR_Law_ClauseNumber, OLR_Law_ItemNumber having count(1) > 1");
            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        public DataTable GetCaseProperty2(string OLR_SituationID, string OLR_Law_ProvID, string OLR_LawID, string OLR_CaseCauseID, string OLR_Law_ClauseNumber, string OLR_Law_ItemNumber)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(string.Format(@" 	 select *    from dbo.SYS_QuoteLawRel
            where OLR_SituationID = '{0}' and OLR_Law_ProvID = '{1}'and OLR_LawID = '{2}' and OLR_CaseCauseID = '{3}' and OLR_Law_ClauseNumber = '{4}' and OLR_Law_ItemNumber = '{5}' ", OLR_SituationID, OLR_Law_ProvID, OLR_LawID, OLR_CaseCauseID, OLR_Law_ClauseNumber, OLR_Law_ItemNumber));
            var ds = oBase.ExecuteDataSet(oCmd);
            return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        /// <summary>
        /// 删除ConvertMode表数据
        /// </summary>
        public bool ConvertModeDeleteConfig(string sql)
        {
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }
    }
}
